import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import CHAR,INT
from py2neo import Graph,Node,Relationship,NodeMatcher,RelationshipMatcher
'''
python3代码, 统一操作内存中的dataframe(即作为数据库和其他数据结构的桥梁)
对于爬取过来的数据， 组织成二维列表， 创建dataframe
对于实体关系数据，编写cql语句，通过py2neo导入Neo4j数据库
对于一般的表（也可能是需要导入的）
下面的注释是参考
'''
# alter table `base_dictmark` add dict_id int not null primary key Auto_increment


# 初始化数据库连接，使用pymysql模块
# MySQL的用户：root, 密码:147369, 端口：3306,数据库：mydb
# engine = create_engine('mysql+pymysql://root:147369@localhost:3306/mydb')

# sql从数据库读取
# pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

# pymysql创建api
# con = pymysql.connect(host=localhost, user=username, password=password, database=dbname, charset='utf8')
# df = pd.read_sql(sql, con)

# dataframe存入mysql
# DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)

# con参数是engine



# https://blog.csdn.net/sinat_26917383/article/details/79901207 操作连接

# 一条路径（Path）（也可以看成是子图）（walkable， 是一种可遍历对象） 是一个节点关系顺序排列的列表，  walk(path) 调用
# 子图 （是边集合和点集合组成的集合） 子图可以取交集， 并集等操作， 还可以通过+， 添加节点或者关系

# graph.run() 执行cql查询语句

# graph.commit() 提交事务

# while cursor.forward():
#     print(cursor.current["name"])

class DataBaseCall:

    '''数据库连接部分'''
    def __init__(self):
        self.mysql = None
        self.neo4j = None
        # self.connect_mysql()
        # self.connect_neo4j()
    def connect_mysql(self):
        self.mysql = create_engine('mysql+pymysql://root:admin@localhost:3306/mydb?charset=utf8', echo=False,encoding='utf-8',convert_unicode=True)
    def connect_neo4j(self):
        self.neo4j = Graph(
            "http://localhost:7474", 
            username="neo4j", 
            password="admin"
        )
        self.node_selector = NodeMatcher(self.neo4j)
        self.rel_selector = RelationshipMatcher(self.neo4j)
    
    '''从数据库或者文件中读取部分'''
    def readFromCQL(self, cql = ''):
        return self.neo4j.run(cql).to_data_frame()

    def readFromSQL(self, sql = ''):
        return pd.read_sql(sql=sql, con=self.mysql)

    def readFromCsv(self, path, sep=','):
        return pd.read_csv(path, sep=sep)
    
    def readFromExcel(self, path):
        return pd.read_excel(path)

    
    '''数据库写入部分'''
    def writeToMySQL(self, df, table_name, method = 'append'):
        df.to_sql(name=table_name, con=self.mysql, if_exists=method, index=False)

    # 更新舆情文章
    def updateArticle(self, df):
        self.writeToMySQL(df,'article')
        baseCQL = '''match (p:Vegetable {title:'%s'})\
        match (q:City {shortName:'%s'})\
        merge (p)<-[r:RelatedVegetable]-(n:Article {title:'%s', date:'%s', text:'%s', city:'%s', veg:'%s' priceSenti:'%s', indicator:'%s'})-[r2:RelatedCity]->(q)\
        '''
        for i in df.index:
            item = df.loc[i]
            cql = baseCQL%(item['Veg'], item['City'], item['Title'], item['Date'], item['Text'], item['City'], item['Veg'], item['PriceSenti'], item['Indicator'])
            # print(cql)
            break
            self.neo4j.run(cql)
    def updateQuotePrice(self, df):
        self.writeToMySQL(df,'price')
        # print('mysql success')
        baseCQL='''match (p:Vegetable {title:'%s'}) match (q:City {title:'%s')\
        merge (p)-[r:QuotePrice{date:'%s', fullCity:'%s', price:'%s', msg:'%s, url:'%s'}]->(q)\
        '''
        for i in df.index:
            item = df.loc[i]
            cql=baseCQL%(item['Date'], item['FullCity'], item['Price'], item['Text'], item['Url'])
            # print(cql)
            break
            self.neo4j.run(cql)

